﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Models;

namespace ChinaDynastyTours.Controls
{
    class CommissionCtrl
    {
        
        internal static DataTable GetCommissionData(string tourgroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string Sql = string.Format(@"SELECT ConsumptionID,c.TourGroupID,c.TourgroupName,c.ShopID,s.ShopName,c.CiceroniName,c.ConsumeDate,c.Population,c.ConsumeSum,c.VerifyPopulation,c.VerifyConsumeSum,c.Price,c.Commission,c.LastRealValue,c.VerifyState,c.Remark  FROM comm_Commission c,bas_Shop s  WHERE c.shopid=s.shopid AND TourgroupID='{0}'", tourgroupId);
            
            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        internal static DataTable GetCommissionData(DateTime dateStart, DateTime dateEnd,string shopId,string verifyState)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string Sql = "";
            if(!string.IsNullOrEmpty(shopId))
                Sql = string.Format(@"SELECT c.*,s.ShopName  FROM comm_Commission c,bas_Shop s WHERE c.shopid=s.shopid AND ConsumeDate BETWEEN '{0}' AND '{1}' AND c.ShopID={2} AND c.VerifyState='{3}'", dateStart.ToString("yyyy/MM/dd"), dateEnd.ToString("yyyy/MM/dd"), shopId, verifyState);
            else
                Sql = string.Format(@"SELECT c.*,s.ShopName  FROM comm_Commission c,bas_Shop s  WHERE c.shopid=s.shopid AND ConsumeDate BETWEEN '{0}' AND '{1}' AND  VerifyState='{2}'", dateStart.ToString("yyyy/MM/dd"), dateEnd.ToString("yyyy/MM/dd"), verifyState);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataTable GetTeamGroupData(DateTime start, DateTime end, string ownerAB)
        {

            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder szCondition = new StringBuilder();

            if (ownerAB != "")//我的团条件，根据当前登录人判定
                szCondition.AppendFormat("(OwnerA='{0}' OR OwnerB='{0}')", ownerAB);
            String Sql = string.Format("SELECT * FROM vw_CommissionTotal WHERE StartDate>='{0}' AND StartDate<='{1}' ", start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"));

            if (szCondition.Length != 0)
                Sql += " AND " + szCondition.AppendFormat(" ORDER BY StartDate");
            else
                Sql += " ORDER BY StartDate";

            DbCommand dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        internal static int CreateCommissionData(CommissionEntity commEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO comm_Commission(
                                        [TourGroupID],
                                        [ShopID],
                                        [CiceroniName],
                                        [ConsumeDate],
                                        [Population],
                                        [ConsumeSum],
                                        [VerifyState],
                                        [Remark],
                                        [TourgroupName],
                                        [Commission],
                                        [Price],
                                        [LastRealValue],
                                        [VerifyConsumeSum],
                                        [VerifyPopulation])
                                        VALUES('{0}', {1}, '{2}', '{3}',{4},{5}, '{6}','{7}','{8}',{9},{10},{11},{12},{13})",
                                        commEntity.TourGroupID,
                                        commEntity.ShopID,
                                        commEntity.CiceroniName,
                                        commEntity.ConsumeDate,
                                        commEntity.Population,
                                        commEntity.ConsumeSum,
                                        commEntity.VerifyState,
                                        commEntity.Remark,
                                        commEntity.TourgroupName,
                                        commEntity.Commission,
                                        commEntity.Price,
                                        commEntity.LastRealValue,
                                        commEntity.VerifyConsumeSum,
                                        commEntity.VerifyPopulation);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 导游填写更新
        /// </summary>
        /// <param name="commEntity"></param>
        /// <returns></returns>
        internal static int UpdateCommissionDataForGuide(CommissionEntity commEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE comm_Commission SET 
                                                [ConsumeDate]='{0}',[Population]={1},
                                                [ConsumeSum]={2},[ShopID]={3},[Remark]='{4}',[CiceroniName]='{5}' 
                                                WHERE ConsumptionID={6}",
                                                commEntity.ConsumeDate,
                                                commEntity.Population,
                                                commEntity.ConsumeSum,
                                                commEntity.ShopID,
                                                commEntity.Remark,
                                                commEntity.CiceroniName,
                                                commEntity.ConsumptionID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        internal static int UpdateCommissionDataForFinance(string consumptionId, string lastRealValue)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE comm_Commission SET [LastRealValue]={0} WHERE ConsumptionID={1}",
                                                lastRealValue,
                                                consumptionId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 计调审核更新
        /// </summary>
        /// <param name="commEntity"></param>
        /// <returns></returns>
        internal static int UpdateCommissionDataForSchedule(CommissionEntity commEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE comm_Commission SET 
                                                [VerifyPopulation]={0},[VerifyConsumeSum]={1},
                                                [Commission]={2},[LastRealValue]={3},[Price]={4} 
                                                WHERE ConsumptionID={5}",
                                                commEntity.VerifyPopulation,
                                                commEntity.VerifyConsumeSum,
                                                commEntity.Commission,
                                                commEntity.LastRealValue,
                                                commEntity.Price,
                                                commEntity.ConsumptionID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        internal static int UpdateCommissionState(string tourgroupId, ShopCommissionEnum commissionState)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE comm_Commission SET [VerifyState]='{0}' WHERE [TourgroupID]='{1}'", commissionState,tourgroupId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        internal static int UpdateCommissionStateToFinal(string consumptionId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE comm_Commission SET [VerifyState]='{0}' WHERE ConsumptionID={1}", ShopCommissionEnum.终结, consumptionId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int DeleteCommissionData(string consumptionId)
        {
            if (consumptionId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM comm_Commission WHERE ConsumptionID={0}", consumptionId);

            return db.ExecuteNonQuery(CommandType.Text, sql);

        }

        #region 统计分析函数
        internal static DataTable GetCommissionDetailistData(DateTime dateStart, DateTime dateEnd,string departmentId,string shopId,string ownerA,string guideName)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            string szCondition = "";
            string Sql = "";

            if (ownerA != "")
                szCondition = " OwnerA='" + ownerA + "' AND";

            if (szCondition != "")
            {
                if (shopId != "")
                    szCondition += " c.ShopID=" + shopId + " AND ";
            }
            else
            {
                if (shopId != "")
                    szCondition += " c.ShopID=" + shopId + " AND ";
            }
            if (szCondition != "")
            {
                if (guideName != "")
                    szCondition += " c.CiceroniName='" + guideName + "' AND ";
            }
            else
            {
                if (guideName != "")
                    szCondition += " c.CiceroniName='" + guideName + "' AND ";
            }
            if (szCondition != "")
            {
                if (departmentId != "")
                    szCondition += " DepartmentID=" + departmentId + " AND ";
            }
            else
            {
                if (departmentId != "")
                    szCondition += " DepartmentID=" + departmentId + " AND ";
            }
            Sql = string.Format(@"SELECT distinct 
	                                      c.[TourgroupID],
                                          c.[TourgroupName],
                                          c.[CiceroniName],
                                          c.[ConsumeDate],
                                          c.[Population],
                                          c.[ConsumeSum],
                                          c.[VerifyPopulation],
                                          c.[VerifyConsumeSum],
                                          c.[Price],
                                          c.[Commission],
                                          c.[LastRealValue],
                                          c.[VerifyState],s.ShopName  FROM comm_Commission c,bas_Shop s,orgt_TourGroup t 
                                    WHERE c.shopid=s.shopid AND c.TourgroupID=t.TourgroupID AND {2} t.StartDate BETWEEN '{0}' AND '{1}' ", dateStart.ToString("yyyy-MM-dd"), dateEnd.ToString("yyyy-MM-dd"), szCondition);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        internal static DataTable GetShopCommissionStatisticsData(DateTime dateStart, DateTime dateEnd, string shopType)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            string Sql = "";
            if(string.IsNullOrEmpty(shopType))
                Sql = string.Format(@"SELECT ShopName,
		                                            SUM(c.VerifyPopulation) as SumVerifyPopulation,
		                                            SUM(c.LastRealValue) as SumLastRealValue ,
		                                            (SUM(c.LastRealValue)/SUM(c.VerifyPopulation)) as AverageLastRealValue
		                                            FROM comm_Commission c,bas_Shop s 
		                                            WHERE c.ShopID=s.ShopID AND c.ConsumeDate between '{0}' and '{1}'  
		                                            GROUP BY s.ShopName", dateStart.ToString("yyyy/MM/dd"), dateEnd.ToString("yyyy/MM/dd"));
            else
                Sql = string.Format(@"SELECT ShopName,
		                                            SUM(c.VerifyPopulation) as SumVerifyPopulation,
		                                            SUM(c.LastRealValue) as SumLastRealValue ,
		                                            (SUM(c.LastRealValue)/SUM(c.VerifyPopulation)) as AverageLastRealValue
		                                            FROM comm_Commission c,bas_Shop s 
		                                            WHERE c.ShopID=s.ShopID AND c.ConsumeDate between '{0}' and '{1}' and s.ShopType='{2}' 
		                                            GROUP BY s.ShopName", dateStart.ToString("yyyy/MM/dd"), dateEnd.ToString("yyyy/MM/dd"), shopType);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        internal static DataTable GetSalesCommissionStatisticsData(DateTime dateStart, DateTime dateEnd)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            //1、从团表取得指定时间段的销售分组的销售名、总团数、应进店团数
            //2、从团表与佣金表联合取得销售分组的销售名、实际进店团数、结算金额的数值
            string Sql = string.Format(@"SELECT OwnerA as 销售, count(TourgroupID) as 总团数,sum(case when ShouleEnterShop=1 then 1 else 0 end) as 应进店团数 from orgt_TourGroup where StartDate between '{0}' and '{1}' group by ownerA;
                                         SELECT t.OwnerA as 销售,count(t.TourgroupID) as 实际进店团数,sum(c.LastRealValue) as 结算金额的数值 FROM orgt_tourgroup t,comm_Commission c where t.tourgroupid=c.tourgroupid  AND  StartDate between '{0}' and '{1}' GROUP BY ownerA", dateStart.ToString("yyyy-MM-dd"), dateEnd.ToString("yyyy-MM-dd"));
            dbCommand = db.GetSqlStringCommand(Sql);
            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables.Count > 0)
            {
                ds.Tables[0].Columns.Add("实际进店团数", typeof(int));
                ds.Tables[0].Columns.Add("结算金额的数值",typeof(decimal));
                ds.Tables[0].Columns.Add("应进店率", typeof(decimal));
                ds.Tables[0].Columns.Add("实际进店率", typeof(decimal));

                foreach (DataRow dr1 in ds.Tables[0].Rows)
                {
                    foreach (DataRow dr2 in ds.Tables[1].Rows)
                    {
                        dr1["应进店率"] = int.Parse(dr1["总团数"].ToString()) == 0 ? 0 : double.Parse(dr1["应进店团数"].ToString()) / double.Parse(dr1["总团数"].ToString());
                        if (dr1["销售"].Equals(dr2["销售"]))
                        { 
                            dr1["实际进店团数"] = dr2["实际进店团数"];
                            dr1["结算金额的数值"] = dr2["结算金额的数值"];
                            dr1["实际进店率"] = int.Parse(dr1["应进店团数"].ToString()) == 0 ? 0 : double.Parse(dr2["实际进店团数"].ToString()) / double.Parse(dr1["应进店团数"].ToString());
                        }
                    }
                    //dr[""] = 0;
                }
            }
            return ds.Tables[0];
        }
        internal static DataTable GetGuidesCommissionStatisticsData(DateTime dateStart, DateTime dateEnd)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            //1、从团表取得指定时间段的销售分组的销售名、总团数、应进店团数
            //2、从团表与佣金表联合取得销售分组的销售名、实际进店团数、结算金额的数值
            string Sql = string.Format(@"SELECT CiceroniName as 导游,count(TourgroupID) as 实际进店团数,sum(LastRealValue) as 结算金额的数值 FROM comm_Commission WHERE ConsumeDate between '{0}' and '{1}' GROUP BY CiceroniName", dateStart.ToString("yyyy/MM/dd"), dateEnd.ToString("yyyy/MM/dd"));
            dbCommand = db.GetSqlStringCommand(Sql);
            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

    }
}
